from flask_sqlalchemy import SQLAlchemy
from datetime import datetime, timedelta

db = SQLAlchemy()

# MySQL ORM模型
class User(db.Model):
    __tablename__ = 'user'
    
    user_id = db.Column(db.Integer, primary_key=True, autoincrement=True)  # 添加 autoincrement=True
    user_name = db.Column(db.String(50))
    user_key = db.Column(db.String(200))
    phonenumber = db.Column(db.String(20))
    register_at = db.Column(db.DateTime, default=datetime.now)
    is_active = db.Column(db.Boolean, default=True)  # 新增，表示用户是否激活
    
class Device(db.Model):
    __tablename__ = 'device'
    
    device_id = db.Column(db.String(64), primary_key=True)
    device_key = db.Column(db.String(128), nullable=True)  # Some devices might not have a key
    user_id = db.Column(db.Integer, db.ForeignKey('user.user_id'), nullable=True)  # 修正类型为Integer和外键引用
    create_at = db.Column(db.DateTime, default=datetime.now)
    is_activated = db.Column(db.Boolean, default=False)
    activation_time = db.Column(db.DateTime, nullable=True)
    
    user = db.relationship('User', backref=db.backref('devices', lazy=True))
    
    def __repr__(self):
        return f'<Device {self.device_id}>'

class Artist(db.Model):
    __tablename__ = 'artist'
    
    artist_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    artist_name = db.Column(db.String(50))
    artist_nationality = db.Column(db.String(50))
    
    # songs = db.relationship('Song', secondary='artist_song', backref='artists')

class Song(db.Model):
    __tablename__ = 'song'
    
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    song_id = db.Column(db.String(50), unique=True, nullable=False)
    song_name = db.Column(db.String(50))
    song_duration = db.Column(db.Numeric(12, 0))
    
    artists = db.relationship('Artist', secondary='artist_song', backref='songs')

class ArtistSong(db.Model):
    __tablename__ = 'artist_song'
    
    artist_id = db.Column(db.Integer, db.ForeignKey('artist.artist_id'), primary_key=True)
    song_id = db.Column(db.Integer, db.ForeignKey('song.id'), primary_key=True)

# 添加管理员模型
class Admin(db.Model):
    __tablename__ = 'admin'
    
    admin_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    admin_name = db.Column(db.String(50), unique=True, nullable=False)
    admin_key = db.Column(db.String(200), nullable=False)  # Changed from admin_password to match SQL
    admin_level = db.Column(db.Integer, default=1, nullable=False)
    create_at = db.Column(db.DateTime, default=datetime.now)
    last_login = db.Column(db.DateTime)
    is_active = db.Column(db.Boolean, default=True)